package com.xiaoxiao.regimen.provider;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

/**
 * Database helper for regimen. Designed as a singleton to make sure that all
 * {@link android.content.ContentProvider} users get the same reference.
 * Provides handy methods for maintaining package and mime-type lookup tables.
 * 
 * @author jaroad
 */
public class RegimenDatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = "RegimenDatabaseHelper";
    
    /**
     * Regimen DB version ranges:
     * <pre>
     * 0-99        XIAOXIAO
     * </pre>
     */
    static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "regimen.db";
    
    public interface Tables {
        public static final String RAW_REGIMEN = "raw_regimen";
        public static final String REGIMEN_TYPES = "regimen_types";
        public static final String MIMETYPES = "mimetypes";
        public static final String DATA = "data";
        public static final String AGGREGATION_EXCEPTIONS = "aggregation_exceptions";
        public static final String GROUPS = "groups";
        public static final String CUSTOM_OPTIONS = "custom_options";
        public static final String SETTINGS = "settings";

        public static final String DATA_JOIN_MIMETYPES = "data "
                + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id)";

        public static final String DATA_JOIN_RAW_REGIMEN = "data "
                + "JOIN raw_regimen ON (data.raw_regimen_id = raw_regimen._id)";

        public static final String DATA_JOIN_MIMETYPE_RAW_REGIMEN = "data "
                + "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) "
                + "JOIN raw_regimen ON (data.raw_regimen_id = raw_regimen._id)";
    }
    
    public interface Views {
        public static final String DATA_ALL = "view_data";

        public static final String RAW_REGIMEN_ALL = "view_raw_regimen";
    }
    
    public interface Clauses {
        // for expand
    }
    
    public interface RegimentypesColumns {
        public static final String _ID = BaseColumns._ID;
        public static final String REGIMEN_TYPE = "regimen_types";

        public static final String CONCRETE_ID = Tables.REGIMEN_TYPES + "." + BaseColumns._ID;
        public static final String CONCRETE_REGIMEN_TYPE = Tables.REGIMEN_TYPES + "." + REGIMEN_TYPE;
    }
    
    public interface MimetypesColumns {
        public static final String _ID = BaseColumns._ID;
        public static final String MIMETYPE = "mimetype";

        public static final String CONCRETE_ID = Tables.MIMETYPES + "." + BaseColumns._ID;
        public static final String CONCRETE_MIMETYPE = Tables.MIMETYPES + "." + MIMETYPE;
    }
    
    public interface RawRegimenColumns {
        public static final String CONCRETE_ID = Tables.RAW_REGIMEN + "." + RawRegimen._ID;
        public static final String CONCRETE_REGIMEN_TYPE_ID = Tables.RAW_REGIMEN + "." + RawRegimen.REGIMEN_TYPE_ID;
    }
    
    public interface DataColumns {
        public static final String MIMETYPE_ID = "mimetype_id";

        public static final String CONCRETE_ID = Tables.DATA + "." + BaseColumns._ID;
        public static final String CONCRETE_MIMETYPE_ID = Tables.DATA + "." + MIMETYPE_ID;
        public static final String CONCRETE_RAW_REGIMEN_ID = Tables.DATA + "."
                + Data.RAW_REGIMEN_ID;

        public static final String CONCRETE_DATA1 = Tables.DATA + "." + Data.DATA1;
        public static final String CONCRETE_DATA2 = Tables.DATA + "." + Data.DATA2;
        public static final String CONCRETE_DATA3 = Tables.DATA + "." + Data.DATA3;
        public static final String CONCRETE_DATA4 = Tables.DATA + "." + Data.DATA4;
        public static final String CONCRETE_DATA5 = Tables.DATA + "." + Data.DATA5;
        public static final String CONCRETE_DATA6 = Tables.DATA + "." + Data.DATA6;
        public static final String CONCRETE_DATA7 = Tables.DATA + "." + Data.DATA7;
        public static final String CONCRETE_DATA8 = Tables.DATA + "." + Data.DATA8;
        public static final String CONCRETE_DATA9 = Tables.DATA + "." + Data.DATA9;
        public static final String CONCRETE_DATA10 = Tables.DATA + "." + Data.DATA10;
        public static final String CONCRETE_DATA11 = Tables.DATA + "." + Data.DATA11;
        public static final String CONCRETE_DATA12 = Tables.DATA + "." + Data.DATA12;
        public static final String CONCRETE_DATA13 = Tables.DATA + "." + Data.DATA13;
        public static final String CONCRETE_DATA14 = Tables.DATA + "." + Data.DATA14;
        public static final String CONCRETE_DATA15 = Tables.DATA + "." + Data.DATA15;
    }
    
    public interface GroupsColumns {
        public static final String CONCRETE_ID = Tables.GROUPS + "." + BaseColumns._ID;
    }

    private final Context mContext;
    
    private boolean mReopenDatabase = false;

    private static RegimenDatabaseHelper sSingleton = null;
    
    public static synchronized RegimenDatabaseHelper getInstance(Context context) {
        if (sSingleton == null) {
            sSingleton = new RegimenDatabaseHelper(context);
        }
        return sSingleton;
    }

    /**
     * Private constructor, callers except unit tests should obtain an instance through
     * {@link #getInstance(android.content.Context)} instead.
     */
    RegimenDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }
    
    /**
     * Called when the database has been opened.  The implementation
     * should check {@link SQLiteDatabase#isReadOnly} before updating the
     * database.
     *
     * @param db The database.
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }
    
    /**
     * Called when the database is created for the first time. This is where the
     * creation of tables and the initial population of the tables should happen.
     *
     * @param db The database.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        
        // regimen types mapping table
        db.execSQL("CREATE TABLE " + Tables.REGIMEN_TYPES + " (" +
                RegimentypesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                RegimentypesColumns.REGIMEN_TYPE + " TEXT NOT NULL" +
        ");");
        
        // regimen types table requires an index on type
        db.execSQL("CREATE UNIQUE INDEX type ON " + Tables.REGIMEN_TYPES + " (" +
                RegimentypesColumns.REGIMEN_TYPE +
        ");");

        // Mimetype mapping table
        db.execSQL("CREATE TABLE " + Tables.MIMETYPES + " (" +
                MimetypesColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                MimetypesColumns.MIMETYPE + " TEXT NOT NULL" +
        ");");
        
        // Mimetype table requires an index on type
        db.execSQL("CREATE UNIQUE INDEX type ON " + Tables.MIMETYPES + " (" +
                MimetypesColumns.MIMETYPE +
        ");");
        
        // regimen table
        db.execSQL("CREATE TABLE " + Tables.RAW_REGIMEN + " (" +
                RawRegimen._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                RawRegimen.REGIMEN_TYPE_ID + " INTEGER REFERENCES regimen_types(_id)," +
                RawRegimen.AGGREGATION_MODE + " INTEGER NOT NULL DEFAULT " +
                    RawRegimen.AGGREGATION_MODE_DEFAULT + ", " +
                RawRegimen.DELETED + " INTEGER NOT NULL DEFAULT 0," +
                RawRegimen.VERSION + " INTEGER NOT NULL DEFAULT 1," +
                RawRegimen.TITLE + " TEXT," +
                RawRegimen.DESCRIPTION + " TEXT," +
                RawRegimen.SORT_KEY + " TEXT," +
                RawRegimen.SORT_KEY_ALT + " TEXT," +
                RawRegimen.DATE + " INTEGER," +
                RawRegimen.CUSTOM_RINGTONE + " TEXT," +
                RawRegimen.STARRED + " INTEGER NOT NULL DEFAULT 0," +
                RawRegimen.TIMES_VIEW + " INTEGER NOT NULL DEFAULT 0," +
                RawRegimen.LAST_TIME_VIEW + " INTEGER, " +
                RawRegimen.SYNC1 + " TEXT, " +
                RawRegimen.SYNC2 + " TEXT, " +
                RawRegimen.SYNC3 + " TEXT, " +
                RawRegimen.SYNC4 + " TEXT, " +
                RawRegimen.SYNC5 + " TEXT " +
        ");");
        
        // Public generic data table
        db.execSQL("CREATE TABLE " + Tables.DATA + " (" +
                Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetypes(_id) NOT NULL," +
                Data.RAW_REGIMEN_ID + " INTEGER REFERENCES raw_regimen(_id) NOT NULL," +
                Data.DATA_VERSION + " INTEGER NOT NULL DEFAULT 0," +
                Data.DATA1 + " TEXT," +
                Data.DATA2 + " TEXT," +
                Data.DATA3 + " TEXT," +
                Data.DATA4 + " TEXT," +
                Data.DATA5 + " TEXT," +
                Data.DATA6 + " TEXT," +
                Data.DATA7 + " TEXT," +
                Data.DATA8 + " TEXT," +
                Data.DATA9 + " TEXT," +
                Data.DATA10 + " TEXT," +
                Data.DATA11 + " TEXT," +
                Data.DATA12 + " TEXT," +
                Data.DATA13 + " TEXT," +
                Data.DATA14 + " TEXT," +
                Data.DATA15 + " TEXT," +
                Data.SYNC1 + " TEXT, " +
                Data.SYNC2 + " TEXT, " +
                Data.SYNC3 + " TEXT, " +
                Data.SYNC4 + " TEXT " +
        ");");
        
        db.execSQL("CREATE INDEX data_raw_regimen_id ON " + Tables.DATA + " (" +
                Data.RAW_REGIMEN_ID +
        ");");
        
        // aggregation table
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.AGGREGATION_EXCEPTIONS + " (" +
                AggregationExceptions._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                AggregationExceptions.TYPE + " INTEGER NOT NULL, " +
                AggregationExceptions.RAW_REGIMEN_ID1
                        + " INTEGER REFERENCES raw_regimen(_id), " +
                AggregationExceptions.RAW_REGIMEN_ID2
                        + " INTEGER REFERENCES raw_regimen(_id)" +
        ");");
        
        db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index1 ON " +
                Tables.AGGREGATION_EXCEPTIONS + " (" +
                AggregationExceptions.RAW_REGIMEN_ID1 + ", " +
                AggregationExceptions.RAW_REGIMEN_ID2 +
        ");");

        db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS aggregation_exception_index2 ON " +
                Tables.AGGREGATION_EXCEPTIONS + " (" +
                AggregationExceptions.RAW_REGIMEN_ID2 + ", " +
                AggregationExceptions.RAW_REGIMEN_ID1 +
        ");");
        
        // Groups table
        db.execSQL("CREATE TABLE " + Tables.GROUPS + " (" +
                Groups._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                Groups.VERSION + " INTEGER NOT NULL DEFAULT 1," +
                Groups.DIRTY + " INTEGER NOT NULL DEFAULT 0," +
                Groups.TITLE + " TEXT," +
                Groups.NOTES + " TEXT," +
                Groups.DELETED + " INTEGER NOT NULL DEFAULT 0," +
                Groups.GROUP_VISIBLE + " INTEGER NOT NULL DEFAULT 0," +
                Groups.GROUP_IS_READ_ONLY + " INTEGER NOT NULL DEFAULT 0," +
                Groups.SYNC1 + " TEXT, " +
                Groups.SYNC2 + " TEXT, " +
                Groups.SYNC3 + " TEXT, " +
                Groups.SYNC4 + " TEXT, " +
                Groups.SYNC5 + " TEXT " +
        ");");
        
        // Custom options table
        db.execSQL("CREATE TABLE " + Tables.CUSTOM_OPTIONS + " (" +
                CustomOptions._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                CustomOptions.RAW_REGIMEN_ID + " INTEGER REFERENCES raw_regimen(_id) NOT NULL," +
                CustomOptions.CUSTOM_RINGTONE + " TEXT," +
                CustomOptions.TIMES_VIEW + " INTEGER NOT NULL DEFAULT 0," +
                CustomOptions.LAST_TIME_VIEW + " INTEGER, " +
                CustomOptions.STARRED + " INTEGER NOT NULL DEFAULT 0 " +
        ");");
        
        db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS custom_options_raw_regimen_id ON " +
                Tables.CUSTOM_OPTIONS + " (" +
                CustomOptions.RAW_REGIMEN_ID +
        ");");
        
        // Settings table
        db.execSQL("CREATE TABLE " + Tables.SETTINGS + " (" +
                Settings._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
        ");");
        
        // We need to close and reopen the database connection so that the stats are
        // taken into account. Make a note of it and do the actual reopening in the
        // getWritableDatabase method.
        mReopenDatabase = true;
        
        // This will create a sqlite_stat1 table that is used for query optimization
        db.execSQL("ANALYZE;");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        
    }

}
